import pandas as pd
from openpyxl import load_workbook
import os


class excel_to:
    def __init__(self, source="", out=""):
        self.source = source
        self.out = out

    def get_head(self, file=""):
        all = []
        df = pd.read_excel(file)
        data = df.loc[1].values
        hm = data[0]
        all.append(hm)
        kh = data[1]
        all.append(kh)
        zh = data[2]
        all.append(zh)
        return all

    def get_data(self, file=""):
        all = []
        df = pd.read_excel(file)
        data = df.loc[2].values
        hm = data[0]
        all.append(hm)
        kh = data[1]
        all.append(kh)
        zh = data[2]
        all.append(zh)
        return all

    def get_all_file(self, source=""):
        tables = []
        path = self.source
        for path, dirs, files in os.walk(path):
            for i in files:
                if i.split(".")[1] == "xlsx":
                    tables.append(i)
        return tables

    def writer_ex(self, file):
        print("执行开始............")
        head = self.get_head(self.source+file)
        data = self.get_data(self.source+file)
        wb = load_workbook(self.source+file)
        wb_s = wb.active  # 激活sheet
        wb_s.insert_rows(1)
        wb_s.insert_cols(1)  # 第一行前面插入一行
        wb_s.insert_cols(2)  # 第二行前面插入一行
        wb_s.insert_cols(3)  # 第三行前面插入一行
        for top_num in range(len(head)):
            for index in range(wb_s.max_column):
                if index == 5 and index > 0:
                    wb_s.cell(index, top_num+1, head[top_num])
                if 5 < index and index > 0:
                    wb_s.cell(index, top_num+1, data[top_num])
        wb.save(self.out+file)  # 保存
        print("写入"+file+"成功............")

    def writer_all(self):
        for file in self.get_all_file():
            self.writer_ex(file)
        print("全部执行完毕..............")

